﻿using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System.Data.SqlClient;
using System.Data;
using Microsoft.Samples.SqlServer.Activities;
using System.Windows.Forms;
using System.Net;

namespace TestProject
{
    public class MyWin32Window : IWin32Window
    {
        private IntPtr _hwnd;

        public MyWin32Window(IntPtr handle)
        {
            _hwnd = handle;
        }

        public IntPtr Handle
        {
            get
            {
                return _hwnd;
            }
        }
    }

    public class SqlAzureSecurity
    {
        public bool Encrypt { get; set; }
        public bool TrustServerCertificate { get; set; }
    }

    public abstract class Schema
    {
        public string SchemaName { get; set; }
        public string Name { get; set; }
        public string DataType { get; set; }
        public int? DataSize { get; set; }
    }

    public class Column : Schema
    {
        public bool IsNullable { get; set; }
    }

    public class Parameter : Schema
    {
        public string Mode { get; set; }
    }

    [TestClass]
    public class SQLServer
    {
        SqlConnectionStringBuilder connStringBuilder = new SqlConnectionStringBuilder();

        public string ConnectionString()
        {
            LoginForm loginForm = new LoginForm();
            loginForm.DataAvailable += new LoginForm.ActivityFormEventHandler(loginForm_DataAvailable);
            loginForm.ShowDialog(new MyWin32Window(System.Diagnostics.Process.GetCurrentProcess().MainWindowHandle));

            return connStringBuilder.ToString();
        }

        void loginForm_DataAvailable(object sender, ActivityFormEventArgs e)
        {
            //Azure SQL Database: {servername}.database.windows.net
            NetworkCredential credentials = (NetworkCredential)e.NetworkCredential;

            if (credentials.Password == string.Empty)
            {
                connStringBuilder.IntegratedSecurity = true;

                //ConnectionBuilderActivity
                connStringBuilder.DataSource = @".";
                connStringBuilder.InitialCatalog = "AdventureWorks2012";
            }
            else
            {
                //Azure SQL Database
                connStringBuilder.Encrypt = true;
                connStringBuilder.TrustServerCertificate = false;

                //Gets UserId and Password from LoginActivity.Credentials
                connStringBuilder.UserID = credentials.UserName;
                connStringBuilder.Password = credentials.Password;

                //ConnectionBuilderActivity
                connStringBuilder.DataSource = "{servername}.database.windows.net";
                connStringBuilder.InitialCatalog = "{database}";
            }
        }

        [TestMethod]
        public void ConnectAzureTest()
        {
            //Act:
            using (SqlConnection connection = new SqlConnection(this.ConnectionString()))
            {
                using (SqlCommand command = connection.CreateCommand())
                {
                    // Setup the command
                    command.CommandText = "SELECT * FROM [Access].[Projects]";
                    command.CommandType = CommandType.Text;

                    connection.Open();

                    using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        reader.Read();

                        //Assert:
                        Assert.IsTrue(reader.HasRows);
                    }
                }
            }
        }

        
        [TestMethod]
        public void AccessTableNames()
        {
            using (SqlConnection conn = new SqlConnection(this.ConnectionString()))
            {
                conn.Open();

                List<string> tables = (from r in conn.GetSchema("TABLES").AsEnumerable()
                    where r.Field<string>("table_schema") == "Access"
                    select String.Format("{0}.{1}", r.Field<string>("table_schema"), r.Field<string>("TABLE_NAME"))).ToList<string>();

                Assert.IsTrue(tables.Count > 0);
            }
        }

        //See: http://msdn.microsoft.com/en-us/library/ms254969.aspx
        [TestMethod]
        public void SchemaNames()
        {
            using (SqlConnection conn = new SqlConnection(this.ConnectionString()))
            {
                conn.Open();

                var schema = from s in
                                 ((from r in conn.GetSchema("TABLES").AsEnumerable()
                                       select r.Field<string>("table_schema")).ToList<string>())
                        group s by s into schemaGroup
                        orderby schemaGroup.Key
                        select schemaGroup;
            }
        }

        [TestMethod]
        public void ViewNames()
        {
            using (SqlConnection conn = new SqlConnection(this.ConnectionString()))
            {
                conn.Open();

                List<string> views = (from r in conn.GetSchema("TABLES").AsEnumerable()
                                      where r.Field<string>("TABLE_TYPE") == "VIEW"
                                      select String.Format("{0}.{1}", r.Field<string>("table_schema"), r.Field<string>("TABLE_NAME"))).ToList<string>();

                Assert.IsTrue(views.Count > 0);
            }
        }

        [TestMethod]
        public void GetTableSchema()
        {
            string InArgument_ViewName = "Access.Projects";

            string[] splitName = InArgument_ViewName.Split(new char[] { '.' });
            string nonSchemaName = splitName.Length > 0 ? splitName[1] : string.Empty;

            using (SqlConnection conn = new SqlConnection(this.ConnectionString()))
            {
                conn.Open();

                List<Column> schema = (from r in conn.GetSchema("Columns").AsEnumerable()
                                           where r.Field<string>("TABLE_NAME") == nonSchemaName
                                           orderby r.Field<int>("ORDINAL_POSITION")
                                           select new Column
                                           {
                                               SchemaName = r.Field<string>("table_schema"),
                                               Name = r.Field<string>("COLUMN_NAME"),
                                               DataType = r.Field<string>("DATA_TYPE"),
                                               DataSize = r.Field<int?>("CHARACTER_MAXIMUM_LENGTH"),
                                               IsNullable = r.Field<string>("IS_NULLABLE") == "YES" ? true : false
                                           }
                                           ).ToList<Column>();

                Assert.IsTrue(schema.Count > 0);
            }
        }

        [TestMethod]
        public void ProcedureNames()
        {
            using (SqlConnection conn = new SqlConnection(this.ConnectionString()))
            {
                conn.Open();

                List<string> procedures = (from r in conn.GetSchema("PROCEDURES").AsEnumerable()
                                           where r.Field<string>("ROUTINE_TYPE") == "PROCEDURE"
                                           select String.Format("{0}.{1}", r.Field<string>("specific_schema"), r.Field<string>("ROUTINE_NAME")))
                                      .ToList<string>();

                Assert.IsTrue(procedures.Count > 0);
            }
        }

        [TestMethod]
        public void ProcedureParameters()
        {
            string InArgument_ProcedureName = "uspGetBillOfMaterials";

            using (SqlConnection conn = new SqlConnection(this.ConnectionString()))
            {
                conn.Open();

                List<Parameter> procedures = (from r in conn.GetSchema("ProcedureParameters").AsEnumerable()
                                              where r.Field<string>("SPECIFIC_NAME") == InArgument_ProcedureName
                                              orderby r.Field<int>("ORDINAL_POSITION")
                                              select new Parameter
                                              {
                                                  SchemaName = r.Field<string>("specific_schema"),
                                                  Name = r.Field<string>("parameter_name"),
                                                  DataType = r.Field<string>("DATA_TYPE"),
                                                  DataSize = r.Field<int?>("CHARACTER_MAXIMUM_LENGTH"),
                                                  Mode = r.Field<string>("parameter_mode")
                                              }
                                                ).ToList<Parameter>();

                Assert.IsTrue(procedures.Count > 0);
            }
        }
    }
}
